﻿<cfsilent>
	<cfscript>
		
		markAdvice = getProperty("serviceFactory").getBean("markAdvice");
		dictAdvice = getProperty("serviceFactory").getBean("senateDictionaryAdvice");
		
		studentId = event.getArg("STU");
		
		sql = "SELECT stu_name FROM t_student WHERE stu_id = :studentId ";
				
		queryObj = new Query( datasource=application.dnsSlave ) ;
   		queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
		
		rs_studentEntity = queryObj.execute( sql=sql ).getResult();
		
		sql = "SELECT 
					t_term.academic_year, 
					t_term.academic_term, 
					t_student_mark.task_type, 
					t_course.course_name, 
					t_course.course_code, 
					t_course.course_credit, 
					t_course_prop.prop_name, 
					t_student_mark.mark_mode, 
					t_student_mark.mark_result, 
					t_student_mark.mark_note, 
					t_student_mark.mark_resit, 
					t_student_mark.mark_renote, 
					t_student_mark.mark_resit_limit, 
					t_student_mark.retaking 
				FROM 
					t_student_mark 
				    INNER JOIN t_term ON t_term.term_id = t_student_mark.term_id 
				    INNER JOIN t_course ON t_course.cid = t_student_mark.cid 
				    INNER JOIN t_course_prop ON t_course_prop.prop_id = t_student_mark.prop_id  
				WHERE 
					t_student_mark.stu_id = :studentId 
				ORDER BY 
					t_term.academic_year DESC, t_term.term_id, t_student_mark.prop_id, t_course.course_name ";
				
		queryObj = new Query( datasource=application.dnsSlave ) ;
   		queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
		
		rs_mark= queryObj.execute( sql=sql ).getResult();
		
	
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat( "学生成绩-" & studentId, "utf-8" );
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew( "成绩信息", false );
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学年,学期,学号,姓名,开课类别,课程,课程号,学分,课程性质,成绩,补考成绩,重修");
	
		for ( i=1; i LTE rs_mark.recordCount; i++ ){
		
			/* markMode, mark, markNote */
			finalResult = markAdvice.getMark( rs_mark["mark_mode"][i], rs_mark["mark_result"][i], rs_mark["mark_note"][i] );

			/* 正考成绩 */
			if ( finalResult eq "" ) {
				finalResult = " ";
			}
			
			/* markMode, mark, markNote, markLimit */
			resitResult = markAdvice.getMarkResit( rs_mark["mark_mode"][i], rs_mark["mark_resit"][i], rs_mark["mark_renote"][i], rs_mark["mark_resit_limit"][i] );
			
			/* 补考成绩 */
			if ( resitResult eq "" ) {
				resitResult = " ";
			}
			
			resit = "";
			if ( rs_mark["retaking"][i] eq "1" ) {
				resit = "是";
			}
			
			/* 构建成绩数据 */
			rowData = rs_mark["academic_year"][i]
						& "," & dictAdvice.getTerm( rs_mark["academic_term"][i] )
						& "," & studentId
						& "," & rs_studentEntity.stu_name
						& "," & dictAdvice.getTaskType( rs_mark["task_type"][i] )
						& "," & rs_mark["course_name"][i]
						& "," & rs_mark["course_code"][i]
						& "," & numberFormat( rs_mark["course_credit"][i], "_.__" )
						& "," & rs_mark["prop_name"][i]
						& "," & finalResult
						& "," & resitResult
						& "," & resit;
			
			/* 写入学生信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);

	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />

</cfsilent>